---
redirect_from:
  - /recipes/passing-dynamic-parameters-in-a-query
---

# Passing dynamic parameters in a query

## Use case

In some cases we may want to let a user select a filter value and be able to 
use that value in calculations without filtering the entire query.

In this example, we want to know the ratio between the number of people in a particular city and
the total number of women in the country. The user can specify the city for the
filter. The trick is to get the value of the city from the user and use it in
the calculation. In the recipe below, we can learn how to join the data table
with itself and reshape the dataset!

## Data modeling

Essentially what we will be doing is cross joining all city values with the rows in the data table. 
This will duplicate each row for every city in the dataset. Then, we will require the user 
to choose a city to filter on, and this will bring us back to our original number of rows. What this 
gives us is a new column in the dataset with the value that the user chose, which we can reference in our metrics. 
In this case, we will use that value to filter a single metric so that we can compare that metric with the whole population.

Let's explore the `users` cube data that contains various information about
users, including city and gender:

| id  | city     | gender | name            |
| --- | -------- | ------ | --------------- |
| 1   | Seattle  | female | Wendell Hamill  |
| 2   | Chicago  | male   | Rahsaan Collins |
| 3   | New York | female | Megane O'Kon    |
| ... | ...      | ...    | ...             |

To calculate the ratio between the number of women in a particular city and the
total number of people in the country, we need to define three measures. One of
them can receive the city value from the filter in a query. Cube will apply this
filter via the `WHERE` clause to the dataset. So, we need to reshape the dataset
so that applying this filter wouldn’t affect the calculations. In this use case,
we can join the data table with itself to multiply the `city` column — applying
the filter would remove the multiplication while still allowing to access the
filter value:

<CodeTabs>

```yaml
cubes:
  - name: users
    sql: >
      WITH data AS (
        SELECT
          users.id AS id,
          users.city AS city,
          users.gender AS gender
        FROM public.users
      ),
      cities AS (
        SELECT city
        FROM data
      ),
      grouped AS (
        SELECT
          cities.city AS city_filter,
          data.id AS id,
          data.city AS city,
          data.gender AS gender
        FROM cities, data
        GROUP BY 1, 2, 3, 4
      )
      SELECT *
      FROM grouped

    measures:
      - name: total_number_of_women
        sql: id
        type: count
        filters:
          - sql: "gender = 'female' and city = city_filter" 

      - name: number_of_people_of_any_gender_in_the_city:
        sql: id
        type: count
        filters:
          - sql: "city = city_filter"

      - name: ratio
        title: Ratio Women in the City to Total Number of People
        sql: >
          1.0 * {number_of_people_of_any_gender_in_the_city} /
          {total_number_of_women}
        type: number

    dimensions:
      - name: city_filter
        sql: city_filter
        type: string
```

```javascript
cube(`users`, {
  sql: `
    WITH data AS (
      SELECT
        users.id AS id,
        users.city AS city,
        users.gender AS gender
      FROM public.users
    ),

    cities AS (
      SELECT city
      FROM data
    ),

    grouped AS (
      SELECT
        cities.city AS city_filter,
        data.id AS id,
        data.city AS city,
        data.gender AS gender
      FROM cities, data
      GROUP BY 1, 2, 3, 4
    )

    SELECT *
    FROM grouped
  `,

  measures: {
    total_number_of_women: {
      sql: "id",
      type: "count",
      filters: [{ sql: `${CUBE}.gender = 'female'` }],
    },

    number_of_people_of_any_gender_in_the_city: {
      sql: "id",
      type: "count",
      filters: [{ sql: `${CUBE}.city = ${CUBE}.city_filter` }],
    },

    ratio: {
      title: "Ratio Women in the City to Total Number of People",
      sql: `
        1.0 * ${CUBE.number_of_people_of_any_gender_in_the_city} /
        ${CUBE.total_number_of_women}`,
      type: `number`,
    },
  },

  dimensions: {
    city_filter: {
      sql: `city_filter`,
      type: `string`,
    },
  },
});
```

</CodeTabs>

## Query

To get the ratio result depending on the city, we need to pass the value via a
filter in the query:

```json
{
  "measures": [
    "users.total_number_of_women",
    "users.number_of_people_of_any_gender_in_the_city",
    "users.ratio"
  ],
  "filters": [
    {
      "member": "users.city_filter",
      "operator": "equals",
      "values": ["Seattle"]
    }
  ]
}
```

## Result

By joining the data table with itself and using the dimensions defined above, we
can get the ratio we wanted to achieve:

```json
[
  {
    "users.total_number_of_women": "259",
    "users.number_of_people_of_any_gender_in_the_city": "99",
    "users.ratio": "0.38223938223938223938"
  }
]
```

## Source code

Please feel free to check out the
[full source code](https://github.com/cube-js/cube/tree/master/examples/recipes/passing-dynamic-parameters-in-query)
or run it with the `docker-compose up` command. You'll see the result, including
queried data, in the console.
